package taper.controller.wcs;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.swing.JOptionPane;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;

import util.Activity;
import util.ErrorMessage;
import util.PropertiesLoader;
import util.common;


import db.DatabaseManager;
import db.EntityWrapper;
import db.entity.Removal;
import db.entity.RemovalPK;
import db.entity.Stir;
import db.entity.StirPK;
import db.entity.TaperSpc;
import db.entity.Tir;
import db.entity.TirPK;
import db.entity.Ttv;
import db.entity.TtvPK;

public class WCSExcelProcessor {
	private static final Logger log = Logger.getLogger(WCSExcelProcessor.class);

	public void startWSCReader() {
		
		new Thread(new Runnable() {
			public void run() {
				while (true) {
					try{
						readFile("Taper");
						readFile("TTV");
						readFile("Removal");
						readFile("STIR");
						readFile("TIR");
						Thread.sleep(PropertiesLoader.getWcsInterval());
					
					} catch (InterruptedException e) {
						log.error("WCSExcelProcessor:startWSCReader",e);
					}
				}
			}
		}).start();
	}

	private void readFile(String type) {
		
		File xlsFile = null;
		InputStream inp = null;
		File dir = new File(PropertiesLoader.getWcsFileDir());
		
		if(!dir.exists()){
			log.info(dir.getName()+" not exist");
			if(dir.mkdir()){
				log.info(" Successful create"+dir.getName());
			}
		}
		
		 FilenameFilter filter = new FileListFilter(type);
		 
		String[] files = dir.list(filter);

		ArrayList filesArrayList = new ArrayList();
		for (String file : files) {
			filesArrayList.add(file);
		}

		Collections.sort(filesArrayList);

		for (int k = 0; k < filesArrayList.size(); k++) {
			String fileName = (String) filesArrayList.get(k);

			log.info("Processing file = " + fileName);
			try {
				xlsFile = new File(PropertiesLoader.getWcsFileDir() + "\\" +fileName);

				if (xlsFile.exists()) {
					log.info(fileName + " File exist");
					Thread.sleep(3000L);

					inp = new FileInputStream(xlsFile);

					Workbook wb = WorkbookFactory.create(inp);

					Sheet sheet = wb.getSheetAt(0);

					Iterator rows = sheet.rowIterator();

					//skip the first row
					if (rows.hasNext()) {
						rows.next();
					}

					boolean read = true;
					while ((read) && (rows.hasNext())) {
						String lotID = null;
						String productID = null;
						String blockID = null;
						String padBatchNo = null;

						double[] fourValue = { 0.0D, 0.0D, 0.0D, 0.0D };

						for (int i = 0; i < 4; i++) //loop 4 times, 4 row insert into 1 db row
													
						{
							if (!rows.hasNext()) {
								read = false;
								if (i == 0) break;
								Activity.log(type+" excel file, data not complete . ", PropertiesLoader.getSystemUser(), type+" excel file, data not complete.System will skip this file");
								//ErrorMessage.displayError(ErrorMessage.WARNING_ERROR, true, type+" excel file, data not complete . ", type+" excel file, data not complete.System will skip this file");
								break;
								
							}

							Row row = (Row) rows.next();

							Cell cell0 = row.getCell(0);
							if (cell0 == null) {
								read = false;

								 if (i == 0) break;
								 Activity.log(type+" excel file, data not complete . ", PropertiesLoader.getSystemUser(), type+" excel file, data not complete.System will skip this file");
								//ErrorMessage.displayError(ErrorMessage.WARNING_ERROR, true, type+" excel file, data not complete . ", type+" excel file, data not complete.System will skip this file");
								break;
							}

							Cell cell1 = row.getCell(1);
							Cell cell6 = row.getCell(6);
							Cell cell8 = row.getCell(8);
							Cell cell9 = row.getCell(9);
							Cell cell10 = row.getCell(10);

							String dcElement = cell0.getStringCellValue().trim();

							String dcElementTP = null;
							if (type.equals("TTV"))
								dcElementTP = dcElement.replace("TTV", "TP");
							else if (type.equals("Removal"))
								dcElementTP = dcElement.replace("PREMDS", "TP");
							else if (type.equals("TIR"))
								dcElementTP = dcElement.replace("TIR", "TP");
							else if (type.equals("STIR"))
								dcElementTP = dcElement.replace("STIR", "TP");
							else if (type.equals("Taper")) {
								dcElementTP = dcElement.replace("TP", "TP");
							}

							productID = common.getAsAlphaNumeric(cell8);
							blockID = common.getAsAlphaNumeric(cell9);
							padBatchNo = common.getAsAlphaNumeric(cell10);
							lotID = common.getAsAlphaNumeric(cell1);

							//String _lotID = lotID;

							fourValue[i] = cell6.getNumericCellValue();


							if (i == 3) {
								i = -1;

								DatabaseManager db = new DatabaseManager();

								List<TaperSpc> taperSPCList = db.listTaperSpcBylotIDdcElement(lotID, dcElementTP);
								
								if (taperSPCList.size() > 0) {
									TaperSpc taperSpc = (TaperSpc) taperSPCList.get(0);
									
									saveSPCData(taperSpc, type, dcElement, lotID, fourValue, productID, blockID, padBatchNo);
								} else {
									
									log.info("No data in db dcElementTP:"+dcElementTP + " lotID :"+lotID);
									
									Activity.log("No such lot ID:"+lotID+" exist in database for the incoming "+type + "_Data.xls file", PropertiesLoader.getSystemUser(), "Either SPC with this lot ID never started, or there is typo in the incoming SPC data file.");
									ErrorMessage.displayError(ErrorMessage.WARNING_ERROR, true, "No such lot ID:"+lotID, "Either SPC with this lot ID never started, or there is typo in the incoming SPC data file.");
									
								}

								for (int j = 0; j < 4; j++) {
									fourValue[j] = 0.0D;
								}
							}
						}

					}

					inp.close();
					if(xlsFile.delete()){
						log.info(xlsFile.getName()+" File deleted");
					}
				}

				Thread.sleep(10000L);
			} catch (Exception e) {
				log.error("readFile",e);

				if (inp != null) {
					try {
						inp.close();
					} catch (Exception ie) {
						ie.printStackTrace();
					}
				}
				if ((xlsFile == null) || (!xlsFile.exists())) {
					continue;
				}
				File errorFolder = new File("C:\\wcsdata\\error\\");
				if (!errorFolder.exists()) {
					errorFolder.mkdirs();
				}

				String errorFileName = "C:\\wcsdata\\error\\" + type + "_error_" + fileName;

				common.copyfile(xlsFile, new File(errorFileName));
				xlsFile.delete();
				
				Activity.log("Error occur while reading "+type + "_Data.xls file", PropertiesLoader.getSystemUser(), "The file renamed to "+errorFileName+ " for checking.");
				ErrorMessage.displayError(ErrorMessage.WARNING_ERROR, true, "Error occur while reading "+type + "_Data.xls file", " The file renamed to "+errorFileName+ " for checking.");
				
			}
		}
	}

	private void saveSPCData(TaperSpc taperSpc, String type, String dcElement, String lotID, double[] fourValue, String productID, String blockID, String padBatchNo) {
	
		if (type.equals("TTV")) {
			Ttv ttv = new Ttv();
			TtvPK ttvPk = new TtvPK();

			ttvPk.setDcElement(dcElement);
			ttvPk.setLotID(lotID);
			
			ttv.setId(ttvPk);
			ttv.setSpcStartDate(taperSpc.getSpcStartDate());
			ttv.setValue1(fourValue[0]);
			ttv.setValue2(fourValue[1]);
			ttv.setValue3(fourValue[2]);
			ttv.setValue4(fourValue[3]);
			ttv.setProductID(productID);
			ttv.setBlockID(blockID);
			ttv.setPadBatchNo(padBatchNo);
			ttv.setMountID(taperSpc.getMountID());

			log.debug("Completed " + type + " " + ttvPk.getLotID() + "," + ttvPk.getDcElement()+"Value:"+fourValue[0]+","+fourValue[1]+","+fourValue[2]+","+fourValue[3]);
			Activity.log("Completed " + type + " " + ttv.getLotID() + "," + ttv.getDCElement(), PropertiesLoader.getSystemUser(), "Data auto received from WCS "+type);
			EntityWrapper<Ttv> db = new EntityWrapper<Ttv>( );
		    db.merge(ttv);
		    db.commit();
		} else if (type.equals("Removal")) {
			Removal removal = new Removal();
			RemovalPK removalPk = new RemovalPK();
			
			removalPk.setDcElement(dcElement);
			removalPk.setLotID(lotID);
			
			removal.setId(removalPk);
			removal.setSpcStartDate(taperSpc.getSpcStartDate());
			removal.setValue1(fourValue[0]);
			removal.setValue2(fourValue[1]);
			removal.setValue3(fourValue[2]);
			removal.setValue4(fourValue[3]);
			removal.setProductID(productID);
			removal.setBlockID(blockID);
			removal.setPadBatchNo(padBatchNo);
			removal.setMountID(taperSpc.getMountID());
			
			//Logging.log(new Date(), "Completed " + type + " " + removal.getLotID() + "," + removal.getDcElement(), "SYSTEM", "Data auto received from WCS " + type);
			log.debug("Completed " + type + " " + removalPk.getLotID() + "," + removalPk.getDcElement()+"Value:"+fourValue[0]+","+fourValue[1]+","+fourValue[2]+","+fourValue[3]);
			Activity.log("Completed " + type + " " + removal.getLotID() + "," + removal.getDCElement(), PropertiesLoader.getSystemUser(), "Data auto received from WCS "+type);
			EntityWrapper<Removal> db = new EntityWrapper<Removal>( );
		    db.merge(removal);
		    db.commit();
		} else if (type.equals("STIR")) {
			Stir stir = new Stir();
			StirPK stirPk = new StirPK();
			
			stirPk.setDcElement(dcElement);
			stirPk.setLotID(lotID);
			
			stir.setId(stirPk);
			stir.setSpcStartDate(taperSpc.getSpcStartDate());
			stir.setValue1(fourValue[0]);
			stir.setValue2(fourValue[1]);
			stir.setValue3(fourValue[2]);
			stir.setValue4(fourValue[3]);

			stir.setProductID(productID);
			stir.setBlockID(blockID);
			stir.setPadBatchNo(padBatchNo);
			stir.setMountID(taperSpc.getMountID());

			//Logging.log(new Date(), "Completed " + type + " " + stir.getLotID() + "," + stir.getDcElement(), "SYSTEM", "Data auto received from WCS " + type);
			log.debug("Completed " + type + " " + stirPk.getLotID() + "," + stirPk.getDcElement()+"Value:"+fourValue[0]+","+fourValue[1]+","+fourValue[2]+","+fourValue[3]);
			Activity.log("Completed " + type + " " + stir.getLotID() + "," + stir.getDCElement(), PropertiesLoader.getSystemUser(), "Data auto received from WCS "+type);
			EntityWrapper<Stir> db = new EntityWrapper<Stir>( );
		    db.merge(stir);
		    db.commit();
		} else if (type.equals("TIR")) {
			Tir tir = new Tir();
			TirPK tirPk = new TirPK();
			
			tirPk.setDcElement(dcElement);
			tirPk.setLotID(lotID);
			
			tir.setId(tirPk);
			tir.setSpcStartDate(taperSpc.getSpcStartDate());
			tir.setValue1(fourValue[0]);
			tir.setValue2(fourValue[1]);
			tir.setValue3(fourValue[2]);
			tir.setValue4(fourValue[3]);

			tir.setProductID(productID);
			tir.setBlockID(blockID);
			tir.setPadBatchNo(padBatchNo);
			tir.setMountID(taperSpc.getMountID());

			//Logging.log(new Date(), "Completed " + type + " " + tir.getLotID() + "," + tir.getDcElement(), "SYSTEM", "Data auto received from WCS " + type);
			log.debug("Completed " + type + " " + tirPk.getLotID() + "," + tirPk.getDcElement()+"Value:"+fourValue[0]+","+fourValue[1]+","+fourValue[2]+","+fourValue[3]);
			Activity.log("Completed " + type + " " + tir.getLotID() + "," + tir.getDCElement(), PropertiesLoader.getSystemUser(), "Data auto received from WCS "+type);
			EntityWrapper<Tir> db = new EntityWrapper<Tir>( );
		    db.merge(tir);
		    db.commit();
		} else if (type.equals("Taper")) {
			taperSpc.setValue1(fourValue[0]);
			taperSpc.setValue2(fourValue[1]);
			taperSpc.setValue3(fourValue[2]);
			taperSpc.setValue4(fourValue[3]);

			taperSpc.setProductID(productID);
			taperSpc.setBlockID(blockID);
			taperSpc.setPadBatchNo(padBatchNo);
			log.debug("Completed " + type + " " + taperSpc.getLotID() + "," + taperSpc.getDCElement()+"Value:"+fourValue[0]+","+fourValue[1]+","+fourValue[2]+","+fourValue[3]);
			Activity.log("Completed SPC " + taperSpc.getLotID() + "," + taperSpc.getDCElement(), PropertiesLoader.getSystemUser(), "Data auto received from WCS SPC");
			//Logging.log(new Date(), "Completed SPC " + taperSpc.getLotID() + "," + taperSpc.getDcElement(), "SYSTEM", "Data auto received from WCS SPC");

			EntityWrapper<TaperSpc> db = new EntityWrapper<TaperSpc>( );
		    db.merge(taperSpc);
		    db.commit();
		}

		//session.close();
	}
	
	class FileListFilter implements FilenameFilter {
		  private String type; 

		  public FileListFilter(String type) {
		    this.type = type;
		  }

		  public boolean accept(File dir, String name) {
			 return name.endsWith(type+"_Data.xls");
		}
		 
	}

}
